<?php


namespace app\common;


use \PhpOffice\PhpSpreadsheet\Cell\Coordinate;

use \PhpOffice\PhpSpreadsheet\IOFactory;
use \PhpOffice\PhpSpreadsheet\Spreadsheet as SpreadsheetClass;

class SpreadSheet
{

    //https://phpspreadsheet.readthedocs.io/en/latest/topics/reading-files/

    public static function write($data, $filename = 'excel', $ext = 'Xlsx', $download = 'brown')
    {

        //$data = array(0 => array(0 => 1, 1 => 'alex1', 2 => 1,), 1 => array(0 => 2, 1 => 'alex2', 2 => 2,), 2 => array(0 => 3, 1 => 'alex3', 2 => 1,), 3 => array(0 => 4, 1 => 'alex4', 2 => 2,), 4 => array(0 => 5, 1 => 'alex5', 2 => 1,), 5 => array(0 => 6, 1 => 'alex6', 2 => 2,));
        $spreadsheet = new SpreadsheetClass();
        $worksheet = $spreadsheet->getActiveSheet();
//设置工作表标题名称
        $worksheet->setTitle('测试Excel');
//表头
//设置单元格内容
//        foreach ($title as $key => $value) {
//            $worksheet->setCellValueByColumnAndRow($key + 1, 1, $value);
//        }
        $row = 1; //第二行开始
        foreach ($data as $item) {
            $column = 1;
            foreach ($item as $value) {
                $worksheet->setCellValueByColumnAndRow($column, $row, $value);
                $column++;
            }
            $row++;
        }
        $writer = IOFactory::createWriter($spreadsheet, $ext);
        if ($ext == 'Xlsx') {
            $ext = 'xlsx';
        }
        if ($ext == 'Xls') {
            $ext = 'xls';
        }
        if ($download == 'brown') {
            # 浏览器下载
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment;filename="' . $filename . '.' . $ext);
            header('Cache-Control: max-age=0');
            $writer->save('php://output');
        } else {
            # 保存为xlsx
            $filename = $filename . '.' . $ext;
            $writer->save($filename);

        }

# 保存为xls
        #       $filename = '测试Excel.xls';
        #       $writer = IOFactory::createWriter($spreadsheet, 'xls'); //xls No writer found for type xls
        #       $writer->save($filename);
# 浏览器下载
//header('Content-Type: application/vnd.ms-excel');
//header('Content-Disposition: attachment;filename="'.$filename.'"');
//header('Cache-Control: max-age=0');
//$writer->save('php://output');

    }



    public static function read($inputFileName){

        $inputFileName = root_path()(). 'public/storage/article/20210115/873994c4252b461a541735a4abd18b13.xls';

        /** Load $inputFileName to a Spreadsheet Object  **/
        //$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xls();
        //$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xls');
        $inputFileType = \PhpOffice\PhpSpreadsheet\IOFactory::identify($inputFileName);
        /**  Create a new Reader of the type that has been identified  **/

        $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);

        $sheetname = 'Data Sheet #2';
        $reader->setLoadSheetsOnly($sheetname);

        ////
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileName);

        $worksheet = $spreadsheet->getActiveSheet();

        $highestRow = $worksheet->getHighestRow(); // 总行数 2
        $highestColumn = $worksheet->getHighestColumn(); // 总列数 B


        # 把列的索引字母转为数字 从1开始 这里返回的是最大列的索引
        # 我尝试了下不用这块代码用以前直接循环字母的方式,拿不到数据
        # 测试了下超过26个字母也是没有问题的
        $highestColumnIndex = Coordinate::columnIndexFromString($highestColumn); //2

        //var_dump($highestColumnIndex);
        $chunk = (int)ceil($highestRow/500);
        dump($chunk);
        $data = [];
        for ($i=1;$i<=$chunk;$i++){
            for ($row = 1+($i-1)*500; $row <= ($i-1)*500+500; ++$row) { // 从第二行开始1-500 501 1000 1001 1500
                $row_data = [];
                for ($column = 1; $column <= $highestColumnIndex; $column++) {
                    $row_data[] = $worksheet->getCellByColumnAndRow($column, $row)->getValue();
                }
                $data[] = unsetNull($row_data);
            }
        }


        var_export($data);

        return $data;


    }

    public function read2(){
        $inputFileType = 'Xls';
        $inputFileName = root_path()(). 'public/storage/article/20210115/873994c4252b461a541735a4abd18b13.xls';

        echo 'Loading file ',pathinfo($inputFileName,PATHINFO_BASENAME),' using IOFactory with a defined reader type of ',$inputFileType,'<br />';
        /**  Create a new Reader of the type defined in $inputFileType  **/
        $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);

        /**  Define how many rows we want to read for each "chunk"  **/
        $chunkSize = 65530;
        /**  Create a new Instance of our Read Filter  **/
        $chunkFilter = new ChunkReadFilter();

        /**  Tell the Reader that we want to use the Read Filter  **/
        /**    and that we want to store it in contiguous rows/columns  **/

        $reader->setReadFilter($chunkFilter)
            ->setContiguous(true);

        /**  Instantiate a new Spreadsheet object manually  **/
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

        /**  Set a sheet index  **/
        $sheet = 0;
        /**  Loop to read our worksheet in "chunk size" blocks  **/
        /**  $startRow is set to 2 initially because we always read the headings in row #1  **/
        for ($startRow = 2; $startRow <= 1000000; $startRow += $chunkSize) {
            /**  Tell the Read Filter which rows we want to read this loop  **/
            $chunkFilter->setRows($startRow,$chunkSize);

            /**  Increment the worksheet index pointer for the Reader  **/
            $reader->setSheetIndex($sheet);
            /**  Load only the rows that match our filter into a new worksheet  **/
            $reader->loadIntoExisting($inputFileName,$spreadsheet);
            /**  Set the worksheet title for the sheet that we've justloaded)  **/
            /**    and increment the sheet index as well  **/
            $spreadsheet->getActiveSheet()->setTitle('Country Data #'.(++$sheet));
        }
    }


    public static function write2($data,$filename = 'excel'){

        $pool = new \Cache\Adapter\Apcu\ApcuCachePool();
        $simpleCache = new \Cache\Bridge\SimpleCache\SimpleCacheBridge($pool);

       \Settings::setCache($simpleCache);
        https://www.cnblogs.com/zx-admin/p/11653863.html
//        set_time_limit(0);
//        for($i =0;$i<=60000;$i++){ // 这样6万条的话 可能
//            $data[] = [
//                'id' => $i+1,
//                'name' => '用户'.($i+1)
//            ];
//        }
//
//        $title = [
//            [
//                '编号', '用户'
//            ],
//        ];
//        $arrData = array_merge($title, $data);
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

// 设置单元格格式 可以省略
        $styleArray = [
            'font' => [
                'bold' => true,
                'size' => 14,
            ],
        ];
//        $spreadsheet->getActiveSheet()->getStyle('A1:B1')->applyFromArray($styleArray);
//        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(25);
//        $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(25);
        $spreadsheet->getActiveSheet()->fromArray($data);
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器输出07Excel文件
// header('Content-Type:application/vnd.ms-excel');//告诉浏览器将要输出Excel03版本文件
        header("Content-Disposition: attachment;filename={$filename}.xlsx");//告诉浏览器输出浏览器名称
        header('Cache-Control: max-age=0');//禁止缓存
        $writer->save('php://output');

    }

    public function export($header,$data,$filename='1.xlsx')
    {
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        foreach ($header as $kh => $vh){
            $sheet->setCellValueByColumnAndRow($kh+1,1, $vh);
        }
        foreach ($data as $k => $v){
            $sheet->setCellValueByColumnAndRow(1,1+$k+1, $v['id']);
        }
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$filename.'"');
        header('Cache-Control: max-age=0');
        // If you're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=1');
        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0

        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        ob_end_flush();
    }
}